/*
****************************************************************************************************************************
*/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('MarketEntityOffers') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	PRINT 'Droping procedure dbo.MarketEntityOffers'
	DROP PROCEDURE dbo.MarketEntityOffers
END
GO

PRINT 'Creating procedure dbo.MarketEntityOffers'
GO

CREATE PROCEDURE dbo.MarketEntityOffers
(
	@memberId UNIQUEIDENTIFIER = NULL
)
AS
BEGIN
	-- Start code added temporarily for LINQ (Auto-generated Type) 
	SET FMTONLY OFF;
	-- End code added temporarily

	IF OBJECT_ID('tempdb..#BidOffer') IS NOT NULL
	BEGIN
		DROP TABLE #BidOffer
	END

	SELECT
		Ent.MarketEntityId, ROW_NUMBER() OVER (PARTITION BY Ent.MarketEntityId 
		ORDER BY OrderPrice DESC, OrderDate) AS RowNumber,
		Ord.OrderId, Ord.TraderId, Ord.OrderPrice, Ord.OrderVolume 
	INTO #BidOffer
	FROM dbo.[Order] Ord
	JOIN dbo.MarketEntity Ent ON Ord.MarketEntityId = Ent.MarketEntityId
	WHERE Ord.Operation = 0 -- Bid
	AND Ord.[Status] = 0 -- Active
	ORDER BY OrderPrice DESC, OrderDate

	IF OBJECT_ID('tempdb..#AskOffer') IS NOT NULL
	BEGIN
		DROP TABLE #AskOffer
	END

	SELECT 
		Ent.MarketEntityId, ROW_NUMBER() OVER (PARTITION BY Ent.MarketEntityId 
		ORDER BY OrderPrice ASC, OrderDate) AS RowNumber,
		Ord.OrderId, Ord.TraderId, Ord.OrderPrice, Ord.OrderVolume 
	INTO #AskOffer
	FROM dbo.[Order] Ord
	JOIN dbo.MarketEntity Ent ON Ord.MarketEntityId = Ent.MarketEntityId
	WHERE Ord.Operation = 1 -- ASK 
	AND Ord.[Status] = 0 -- Active
	ORDER BY OrderPrice ASC, OrderDate

	IF OBJECT_ID('tempdb..#LastTrade') IS NOT NULL
	BEGIN
		DROP TABLE #LastTrade
	END

	SELECT
		Ent.MarketEntityId, ROW_NUMBER() 
			OVER (PARTITION BY Ent.MarketEntityId ORDER BY TA.ActivityDate DESC) AS RowNumber
		,TA.ActivityDate AS LastDate
		,TA.Price AS LastPrice
	INTO #LastTrade
	FROM dbo.[TradeActivity] TA
	JOIN dbo.MarketEntity Ent ON TA.MarketEntityId = Ent.MarketEntityId
	ORDER BY ActivityDate


	IF OBJECT_ID('tempdb..#CalcBidOffer') IS NOT NULL
	BEGIN
		DROP TABLE #CalcBidOffer
	END

	SELECT 
		Ent.MarketEntityId, 
		ROW_NUMBER() OVER (PARTITION BY Ent.MarketEntityId 
		ORDER BY OrderPrice DESC, OrderDate) AS RowNumber,
		Ord.OrderPrice,
		Ord.OrderId
	INTO #CalcBidOffer
	FROM dbo.[Order] Ord
	JOIN dbo.MarketEntity Ent ON Ord.MarketEntityId = Ent.MarketEntityId
	WHERE Ord.Operation = 0 -- Bid
	AND Ord.[Status] = 0 -- Active
	ORDER BY OrderPrice DESC, OrderDate

	IF OBJECT_ID('tempdb..#CalcAskOffer') IS NOT NULL
	BEGIN
		DROP TABLE #CalcAskOffer
	END

	SELECT 
		Ent.MarketEntityId, 
		ROW_NUMBER() OVER (PARTITION BY Ent.MarketEntityId 
		ORDER BY OrderPrice ASC, OrderDate) AS RowNumber,
		Ord.OrderPrice,
		Ord.OrderId
	INTO #CalcAskOffer
	FROM dbo.[Order] Ord
	JOIN dbo.MarketEntity Ent ON Ord.MarketEntityId = Ent.MarketEntityId
	WHERE Ord.Operation = 1 -- ASK 
	AND Ord.[Status] = 0 -- Active
	ORDER BY OrderPrice ASC, OrderDate

	DELETE FROM #CalcBidOffer WHERE RowNumber > 1
	DELETE FROM #CalcAskOffer WHERE RowNumber > 1

	IF OBJECT_ID('tempdb..#TradeConfirm') IS NOT NULL
	BEGIN
		DROP TABLE #TradeConfirm
	END	

	SELECT 
		Main.MarketEntityId
		,Bid.OrderId AS BidOrderId
		,Bid.OrderPrice AS BidPrice
		,Ask.OrderId AS AskOrderId
		,Ask.OrderPrice AS AskPrice
	INTO #TradeConfirm
	FROM dbo.MarketEntity Main
	LEFT OUTER JOIN #CalcBidOffer AS Bid ON Main.MarketEntityId = Bid.MarketEntityId
	LEFT OUTER JOIN #CalcAskOffer AS Ask ON Main.MarketEntityId = Ask.MarketEntityId
	WHERE Ask.OrderId IS NOT NULL AND Bid.OrderId IS NOT NULL
	AND Bid.OrderPrice = Ask.OrderPrice


	SELECT Ent.MarketEntityId, 
			ISNULL(BidOffer3.OrderPrice,0.0) AS BidPrice3,
			ISNULL(BidOffer3.OrderVolume,0.0) AS BidVolume3,
			BidOffer3.OrderId AS BidOrderId3,
			BidOffer3.TraderId AS BidTraderId3,
			ISNULL(BidOffer2.OrderPrice,0.0) AS BidPrice2,
			ISNULL(BidOffer2.OrderVolume,0.0) AS BidVolume2, 
			BidOffer2.OrderId AS BidOrderId2,
			BidOffer2.TraderId AS BidTraderId2,
			ISNULL(BidOffer1.OrderPrice,0.0) AS BidPrice1,
			ISNULL(BidOffer1.OrderVolume,0.0) AS BidVolume1, 
			BidOffer1.OrderId AS BidOrderId1,
			BidOffer1.TraderId AS BidTraderId1,
			ISNULL(AskOffer1.OrderPrice,0.0) AS AskPrice1,
			ISNULL(AskOffer1.OrderVolume,0.0) AS AskVolume1,
			AskOffer1.OrderId AS AskOrderId1,
			AskOffer1.TraderId AS AskTraderId1,
			ISNULL(AskOffer2.OrderPrice,0.0) AS AskPrice2,
			ISNULL(AskOffer2.OrderVolume,0.0) AS AskVolume2, 
			AskOffer2.OrderId AS AskOrderId2,
			AskOffer2.TraderId AS AskTraderId2,
			ISNULL(AskOffer3.OrderPrice,0.0) AS AskPrice3,
			ISNULL(AskOffer3.OrderVolume,0.0) AS AskVolume3,
			AskOffer3.OrderId AS AskOrderId3,
			AskOffer3.TraderId AS AskTraderId3,
			ISNULL(LastIndex.LastPrice,0) AS IndexPrice,
			LastOffer.LastDate,
			LastOffer.LastPrice,
			TradeConfirm.BidOrderId AS BidOrderId,
			TradeConfirm.AskOrderId AS AskOrderId
	FROM dbo.MarketEntity Ent
	LEFT OUTER JOIN #TradeConfirm AS TradeConfirm ON 
		Ent.MarketEntityId = TradeConfirm.MarketEntityId
	LEFT OUTER JOIN #LastTrade AS LastOffer ON Ent.MarketEntityId = LastOffer.MarketEntityId AND LastOffer.RowNumber = 1
	LEFT OUTER JOIN #LastTrade AS LastIndex ON Ent.MarketEntityId = LastIndex.MarketEntityId AND LastIndex.RowNumber = 2
	LEFT OUTER JOIN #AskOffer AS AskOffer1 ON 
		Ent.MarketEntityId = AskOffer1.MarketEntityId AND AskOffer1.RowNumber = 1
	LEFT OUTER JOIN #AskOffer AS AskOffer2 ON 
		Ent.MarketEntityId = AskOffer2.MarketEntityId AND AskOffer2.RowNumber = 2
	LEFT OUTER JOIN #AskOffer AS AskOffer3 ON 
		Ent.MarketEntityId = AskOffer3.MarketEntityId AND AskOffer3.RowNumber = 3
	LEFT OUTER JOIN #BidOffer AS BidOffer1 ON 
		Ent.MarketEntityId = BidOffer1.MarketEntityId AND BidOffer1.RowNumber = 1
	LEFT OUTER JOIN #BidOffer AS BidOffer2 ON 
		Ent.MarketEntityId = BidOffer2.MarketEntityId AND BidOffer2.RowNumber = 2
	LEFT OUTER JOIN #BidOffer AS BidOffer3 ON 
		Ent.MarketEntityId = BidOffer3.MarketEntityId AND BidOffer3.RowNumber = 3
END
GO